﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Convert.DataAccess;
using Convert.LOTSmodel;
using Convert.CPIAMmodel;
using System.Configuration;
using System.Data.OleDb;
using System.Windows;
namespace Convert.DBconverter
{
    class StaffConverter
    {
        
        //private static int BATCH_SAVE_SIZE = 5000;
        public static string LotsConnectionString; //= @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\LOTS\lots.mdb";//ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.lotsConnectionString"].ConnectionString;
        public static string CpiamConnectionString; //= ConfigurationManager.ConnectionStrings["LOTStoCPIAMconvert.Properties.Settings.CPIAMPOSMaxRetailConnectionString"].ConnectionString;
        public static Dictionary<int, Guid> LotsClerkIdsToCPIAMIDs;
        public static Dictionary<int, Guid> ConvertStaff(string lotsConnectionString, string cpiamConnectionString)
        {
            LotsConnectionString = lotsConnectionString;
            CpiamConnectionString = cpiamConnectionString;
            DataTable ClerkFileDT = DataTables.CreateClerkDT();
            LotsClerkIdsToCPIAMIDs = new Dictionary<int, Guid>();
            OleDbConnection dbConn = new OleDbConnection(LotsConnectionString);
            try
            {
                dbConn.Open();
                using (OleDbCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"SELECT Staff.StaffID, Staff.StaffInitial, Staff.StaffName, Staff.SecurityLevelID, Staff.InactiveStaff, SecurityLevel.SecurityLevelName 
                    FROM (Staff INNER JOIN SecurityLevel ON Staff.SecurityLevelID = SecurityLevel.SecurityLevelID)
                    WHERE (NOT (Staff.StaffName LIKE '%Pharmasol%')) AND (Staff.StaffName <> 'Owner') AND (Staff.StaffName <> 'Support') AND (Staff.InactiveStaff = false)");
                    OleDbDataReader reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                            Staff staff = new Staff();
                            int i = 0;
                            staff.StaffID = reader.GetInt32(i);
                            staff.StaffInitial = reader.IsDBNull(++i) ? "" : reader.GetString(i);
                            staff.StaffName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            staff.SecurityLevelID = reader.GetInt32(++i);
                            staff.InactiveStaff = reader.GetBoolean(++i);
                            staff.SecurityLevelName = reader.IsDBNull(++i) ? null : reader.GetString(i);
                            SaveStaff(staff, ClerkFileDT);
                        //if (ClerkFileDT.Rows.Count % BATCH_SAVE_SIZE == 0)
                        //{
                          //  SaveAllDataTables(CpiamConnectionString, ClerkFileDT);
                        //}
                    }
                    //SaveAllDataTables(CpiamConnectionString, ClerkFileDT);
                }
            }
            catch (Exception ex)
            {
                Logger.LogWarningAndError("Error in Convert Staff ", ex);
                MessageBox.Show("Error converting Staff: " + ex.Message + "\n\nStackTrace=" + ex.StackTrace);
            }
            finally
            {
                dbConn.Close();
            }
            return LotsClerkIdsToCPIAMIDs;
        }

        private static void SaveAllDataTables(String ConnectionString, DataTable ClerkFileDT)
        {
            if (ClerkFileDT.Rows.Count > 0)
                dbSaveBatch.SaveBatch(ClerkFileDT, "ClerkFile", ConnectionString);
        }

        private static void SaveStaff(Staff staff, DataTable ClerkFileDT)
        {
            //ClerkFile clerkFile = new ClerkFile();
            //clerkFile.ClerkID = Guid.NewGuid();
            //clerkFile.ClerkSurname = "";
            //clerkFile.ClerkFirstName = staff.StaffName;
            //clerkFile.ClerkPassWord = "0000";
            //clerkFile.ClerkPWActive = false;
            //clerkFile.ClerkCode = staff.StaffID.ToString();
            //clerkFile.ClerkPhone = "0000000000";
            //clerkFile.ClerkStartDate = DateTime.Now;
            //clerkFile.ClerkGroup = "Staff Member";
            //clerkFile.ClerkRegistrationNo = "N/A";
            //clerkFile.ClerkInitials = staff.StaffInitial.ToUpper();
            //if (staff.SecurityLevelName == "Administrator")
            //    clerkFile.ClerkGroup = "Owner";
            //else if (staff.SecurityLevelName == "Manager" || staff.SecurityLevelName == "Senior")
            //    clerkFile.ClerkGroup = "Manager";
            //else
            //    clerkFile.ClerkGroup = "Staff Member";
            //ClerkFileDT.Rows.Add(clerkFile.ClerkID, clerkFile.ClerkSurname, clerkFile.ClerkFirstName, clerkFile.ClerkPassWord, clerkFile.ClerkPWActive, clerkFile.ClerkCode, clerkFile.ClerkPhone, clerkFile.ClerkStartDate, clerkFile.ClerkGroup, clerkFile.ClerkRegistrationNo, clerkFile.ClerkInitials);

            string clerkGroup;
            if (staff.SecurityLevelName == "Administrator")
                clerkGroup = "Owner";
            else if (staff.SecurityLevelName == "Manager" || staff.SecurityLevelName == "Senior")
                clerkGroup = "Manager";
            else
                clerkGroup = "Staff Member";

            Guid ClientID = Guid.NewGuid();
            ClerkFile clerkFile = new ClerkFile(ClientID, null, staff.StaffName, staff.StaffID.ToString(), null, clerkGroup, staff.StaffInitial.ToUpper());
            int intNewClerkID = insertClerk(clerkFile);
            clerkFile = new ClerkFile();
            clerkFile.ClerkSurname = String.Empty;
            clerkFile.ClerkFirstName = staff.StaffName;
            clerkFile.ClerkCode = Encryption.EncryptData(staff.StaffID.ToString(), false, Encryption.GetClerkSaltedEncryptKey(intNewClerkID.ToString()));
            clerkFile.ClerkPassWord = Encryption.EncryptData("0000", false, Encryption.GetClerkSaltedEncryptKey(intNewClerkID.ToString()));
            clerkFile.ClerkPhone = Encryption.EncryptData("0000000000", false, Encryption.GetClerkSaltedEncryptKey(intNewClerkID.ToString()));
            clerkFile.ClerkGroup = Encryption.EncryptData(clerkGroup, false, Encryption.GetClerkSaltedEncryptKey(intNewClerkID.ToString()));
            clerkFile.ClerkRegistrationNo = "N/A";
            clerkFile.ClerkInitials = staff.StaffInitial.ToUpper();
            clerkFile.ClerkID = ClientID;
            updateClerk(clerkFile);
            //ClerkFile.SaveClerkFileToDataTable(clerkFile, ClerkFileDT);
            LotsClerkIdsToCPIAMIDs.Add(staff.StaffID, clerkFile.ClerkID);
        }

        public static int insertClerk(ClerkFile clerkFile)
        {
            int ClerkNo = 0;
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            try
            {
                dbConn.Open();
                using (SqlCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"INSERT INTO [ClerkFile] ([ClerkID], [ClerkSurname], [ClerkFirstName], [ClerkPassWord], [ClerkPWActive], [ClerkCode], [ClerkPhone], [ClerkStartDate], [ClerkStatus], [ClerkGroup], [ClerkRegistrationNo], [ClerkInitials]) 
                                                        VALUES (@ClerkID, @ClerkSurname, @ClerkFirstName, @ClerkPassWord, @ClerkPWActive, @ClerkCode, @ClerkPhone, @ClerkStartDate, @ClerkStatus, @ClerkGroup, @ClerkRegistrationNo, @ClerkInitials);
                                                        SELECT SCOPE_IDENTITY()");
                    command.Parameters.AddWithValue("@ClerkID", clerkFile.ClerkID);
                    command.Parameters.AddWithValue("@ClerkSurname", clerkFile.ClerkSurname);
                    command.Parameters.AddWithValue("@ClerkFirstName", clerkFile.ClerkFirstName);
                    command.Parameters.AddWithValue("@ClerkPassWord", clerkFile.ClerkPassWord);
                    command.Parameters.AddWithValue("@ClerkPWActive", clerkFile.ClerkPWActive);
                    command.Parameters.AddWithValue("@ClerkCode", clerkFile.ClerkCode);
                    command.Parameters.AddWithValue("@ClerkPhone", clerkFile.ClerkPhone);
                    command.Parameters.AddWithValue("@ClerkStartDate", clerkFile.ClerkStartDate);
                    command.Parameters.AddWithValue("@ClerkStatus", clerkFile.ClerkStatus);
                    command.Parameters.AddWithValue("@ClerkGroup", clerkFile.ClerkGroup);
                    command.Parameters.AddWithValue("@ClerkRegistrationNo", clerkFile.ClerkRegistrationNo);
                    command.Parameters.AddWithValue("@ClerkInitials", clerkFile.ClerkInitials);
                    ClerkNo = System.Convert.ToInt32(command.ExecuteScalar());
                }
            }
            finally
            {
                dbConn.Close();
            }
            return ClerkNo; 
        }

        public static void updateClerk(ClerkFile clerkFile)
        {
            SqlConnection dbConn = new SqlConnection(CpiamConnectionString);
            try
            {
                dbConn.Open();
                using (SqlCommand command = dbConn.CreateCommand())
                {
                    command.CommandText = string.Format(@"UPDATE ClerkFile
                        SET ClerkSurname = @ClerkSurname, ClerkFirstName = @ClerkFirstName, ClerkCode = @ClerkCode, ClerkPassWord = @ClerkPassWord, 
                        ClerkPhone = @ClerkPhone, ClerkGroup = @ClerkGroup, ClerkRegistrationNo = @ClerkRegistrationNo, ClerkInitials = @ClerkInitials
                        WHERE (ClerkID = @ClerkID)");
                    command.Parameters.AddWithValue("@ClerkSurname", clerkFile.ClerkSurname);
                    command.Parameters.AddWithValue("@ClerkFirstName", clerkFile.ClerkFirstName);
                    command.Parameters.AddWithValue("@ClerkPassWord", clerkFile.ClerkPassWord);
                    command.Parameters.AddWithValue("@ClerkCode", clerkFile.ClerkCode);
                    command.Parameters.AddWithValue("@ClerkPhone", clerkFile.ClerkPhone);
                    command.Parameters.AddWithValue("@ClerkGroup", clerkFile.ClerkGroup);
                    command.Parameters.AddWithValue("@ClerkRegistrationNo", clerkFile.ClerkRegistrationNo);
                    command.Parameters.AddWithValue("@ClerkInitials", clerkFile.ClerkInitials);
                    command.Parameters.AddWithValue("@ClerkID", clerkFile.ClerkID);
                    command.ExecuteNonQuery();
                }
            }
            finally
            {
                dbConn.Close();
            }
        }
    }
}

